package com.pub.dbtools;

/**
 * Title: SQL语句操作相关静态方法(操作工作数据库)
 * Desc:
 */
import java.io.File;
import java.io.FileInputStream;
import java.net.ProxySelector;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.jeecgframework.core.util.DateUtils;
import org.jeecgframework.core.util.PropertiesUtil;


public class DBTools {

	/**
	 * 取得数据库连接
	 * 
	 * @param nowConn
	 *            Connect 连接名称
	 */
	public static final Connection getConnect(String dbname) {
		try {
			PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
			String driver = tmpvo.readProperty("jdbc.driver");
			String currentweb = "tomcat";
			String url = tmpvo.readProperty( "jdbc.url");
			String user = tmpvo.readProperty( "jdbc.username");
			String password = tmpvo.readProperty( "jdbc.password");

			if (dbname.toLowerCase().trim().startsWith("oracle")) {
				Class.forName(driver);
				if (currentweb.equalsIgnoreCase("websphere")) {
					ProxySelector ps = ProxySelector.getDefault();
					ProxySelector.setDefault(null);
					Connection con = DriverManager.getConnection(url, user,
							password);
					ProxySelector.setDefault(ps);
					return con;
				} else {
					Connection con = DriverManager.getConnection(url, user,
							password);
					return con;
				}
			}
			if (dbname.toLowerCase().trim().startsWith("basedb")) {
				if (dbname.toLowerCase().trim().equals("basedbtx")) {
					user = "root";
					password = "dbbase";
				}
				Class.forName(driver);
				String urlstr = url;
				Connection con = DriverManager.getConnection(urlstr,
						user, password);

				return con;
			}

			if (dbname.toLowerCase().trim().startsWith("mysql")) {
				Class.forName(driver);
				String urlstr = url;
				Connection con = DriverManager.getConnection(urlstr,
						user, password);

				return con;
			}
			if (dbname.toLowerCase().trim().startsWith("db2")) {

			}
			if (dbname.toLowerCase().trim().startsWith("sqlserver")) {

			}
			return null;
		} catch (Throwable t) {
			t.printStackTrace();
			return null;
		} finally {
		}
	}

	/**
	 * 取得定义数据库连接
	 * 
	 * @param nowConn
	 *            Connect 连接名称
	 */
	public static final Connection getConnect(DataSimpDto dbdto) {
		String fd_mc = dbdto.getDataValue("fd_mc", 0);
		String fd_sjljms = dbdto.getDataValue("fd_sjljms", 0);

		try {
			String driver = dbdto.getDataValue("fd_sjklx", 0);

			if (driver.equalsIgnoreCase("ORACLE")) {
				driver = "oracle.jdbc.driver.OracleDriver";
			}
			if (driver.equalsIgnoreCase("DB2")) {
				driver = "com.ibm.db2.jdbc.app.DB2Driver";
			}
			if (driver.equalsIgnoreCase("SQLSERVER")) {
				driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
			}
			if (driver.equalsIgnoreCase("MYSQL")) {
				driver = "org.gjt.mm.mysql.Driver";
			}
			if (driver.equalsIgnoreCase("sybase")) {
				driver = "com.sybase.jdbc.SybDriver";
			}
			Class.forName(driver);

			String url = dbdto.getDataValue("fd_ljmsf", 0);
			String user = dbdto.getDataValue("fd_ljzh", 0);
			String password = dbdto.getDataValue("fd_ljmm", 0);

			return DriverManager.getConnection(url, user, password);
		} catch (Throwable t) {
			try {
				TablePk tmppk = TablePk.getInstance();
				String cwms = t.toString();
				if (cwms.length() > 200) {
					cwms = cwms.substring(0, 200);
				}
				String intsql = "insert into t_bbdyq_sjljmx(fd_sjljmx_id,fd_sjljms,fd_mc,fd_ljsj,fd_qkms) values(";
				intsql += "'" + tmppk.getNextValue("t_bbdyq_sjljmx") + "','"
						+ fd_sjljms + "','" + fd_mc + "','"
						+ DateUtils.getDatetime() + "','" + cwms + "')";
				DBOperator.execSql(intsql);

			} catch (Throwable t1) {
				t1.printStackTrace();
			}
			t.printStackTrace();
			return null;
		} finally {
		}
	}

	/**
	 * 执行表DML语句操作
	 * 
	 * @String DML语句
	 * @Connect 表连接
	 * @Return String ok/error
	 */
	public static final String execDML(String strSql) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname =tmpvo.readProperty( "dbType");

		Connection con = getConnect(dbname);
		if (con == null)
			return "error";
		Statement stmtDB;
		ResultSet rsetTemp;
		int nCode;
		String tableName = "";
		try {
			stmtDB = con.createStatement();
			String strTemp = strSql.toLowerCase().trim();

			if (strTemp.startsWith("create") || strTemp.startsWith("drop")
					|| strTemp.startsWith("alter")
					|| strTemp.startsWith("delete")
					|| strTemp.startsWith("insert")
					|| strTemp.startsWith("comment")
					|| strTemp.startsWith("update")) {

				tableName = strTemp;
				stmtDB.executeUpdate(strSql);
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				con.close();
				con = null;
				return "ok";
			} else {
				System.err.println("not dml format!");
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				con.close();
				con = null;
				return "error";
			}
		} catch (SQLException e) {
			e.printStackTrace();
			stmtDB = null;
			rsetTemp = null;
			con = null;
			return e.toString();
		} finally {
			stmtDB = null;
			rsetTemp = null;
			con = null;
		}
	}

	/**
	 * 执行数据表 增删改SQL 事务
	 * 
	 * @param ArrayList
	 *            执行操作语句组
	 * @return String ok--成功 error--未成功
	 */
	public static final String execSql(ArrayList sqls) {
		if (sqls.size() < 1) {
			return "ok";
		}

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname =tmpvo.readProperty( "dbType");
		String debugsign = tmpvo.readProperty("debugsign");

		Statement stmtDB = null;


		Connection con = getConnect(dbname);
		if (con == null)
			return "error";

		try {
			con.setAutoCommit(false);
			stmtDB = con.createStatement();
			String strSql = "";
			for (int i = 0; i < sqls.size(); i++) {
				if (debugsign.toLowerCase().equals("yes")) {
					System.err.println((String) sqls.get(i));
				}
				strSql = ((String) sqls.get(i)).trim();

				if (strSql.endsWith(";")) {
					strSql = strSql.substring(0, strSql.length() - 1);
				}
				stmtDB.addBatch(strSql);
			}
			stmtDB.executeBatch();
			con.commit();

			con.setAutoCommit(true);

			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (SQLException se) {
			se.printStackTrace();
			try {
				con.rollback();
				con.setAutoCommit(true);
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改 单句操作
	 * 
	 * @param str
	 *            执行操作语句
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 * 
	 */
	public static final String execSql(String str) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname =tmpvo.readProperty( "dbType");
		String debugsign = tmpvo.readProperty("debugsign");

		Statement stmtDB = null;
		Connection con = getConnect(dbname);
		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			if (debugsign.toLowerCase().equals("yes")) {
				System.err.println(str);
			}
			stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改 单句操作 补充
	 * 
	 * @param str
	 *            执行操作语句
	 * @return-- (String) "zero" 影响0条记录 "ok" 成功 "error" 未成功)
	 * 
	 */
	public static final String execSqlComple(String str) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname =tmpvo.readProperty( "dbType");
		String debugsign = tmpvo.readProperty("debugsign");

		Statement stmtDB = null;
		Connection con = getConnect(dbname);
		int results = 0;

		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			if (debugsign.toLowerCase().equals("yes")) {
				System.err.println(str);
			}
			results = stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			if (results == 0) {
				return "zero";
			} else {
				return "ok";
			}
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改 单句操作(带数据库名称)
	 * 
	 * @param str
	 *            执行操作语句
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 * 
	 */
	public static final String execSql(String dbname, String str) {
		Statement stmtDB = null;
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String debugsign = tmpvo.readProperty("debugsign");
		Connection con = getConnect(dbname);
		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			if (debugsign.toLowerCase().equals("yes")) {
				System.err.println(str);
			}
			stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改SQL 事务
	 * 
	 * @param ArrayList
	 *            执行操作语句组
	 * @return String ok--成功 error--未成功
	 */
	public static final String execSql(String dbname, ArrayList sqls) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String debugsign = tmpvo.readProperty("debugsign");
		Statement stmtDB = null;

		if (sqls.size() < 1) {
			return "ok";
		}
		Connection con = getConnect(dbname);
		if (con == null)
			return "error";

		try {
			con.setAutoCommit(false);
			stmtDB = con.createStatement();
			String strSql = "";
			for (int i = 0; i < sqls.size(); i++) {
				if (debugsign.toLowerCase().equals("yes")) {
					System.err.println((String) sqls.get(i));
				}
				strSql = (String) sqls.get(i);
				if (strSql.endsWith(";")) {
					strSql = strSql.substring(0, strSql.length() - 1);
				}
				stmtDB.addBatch(strSql);
			}
			stmtDB.executeBatch();
			con.commit();

			con.setAutoCommit(true);

			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (SQLException se) {
			se.printStackTrace();
			try {
				con.rollback();
				con.setAutoCommit(true);
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 构造分页结果集SQL语句
	 * 
	 * @param sql
	 *            ：select 语句
	 * @param fromsql
	 *            : from 语句
	 * @param wheresql
	 *            : where 语句
	 * @param orderSql
	 *            : ORDER 语句
	 * @param startIndex
	 *            : 起始位置
	 * @param pageSize
	 *            : 每页记录数
	 * @param groupsql
	 *            : GROUP 语句
	 * @param addsql
	 *            : addsql 语句
	 * @return String
	 */
	public static final String initQuerySQL(String sql, String fromsql,
			String wheresql, String orderSql, int startIndex, int pageSize,
			String groupsql, String addsql) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String currdb =tmpvo.readProperty( "dbType");



		if (currdb.equalsIgnoreCase("oracle")) {
			StringBuffer querySQL = new StringBuffer();
			int totrow = pageSize + startIndex;
			querySQL.append(
					"select * from (select my_table.*,rownum as my_rownum from(")
					.append(sql + " " + fromsql + " " + wheresql + " "
							+ groupsql + " " + orderSql).append(") my_table ")
					.append(") where my_rownum>")
					.append(startIndex + " and my_rownum<=" + totrow);

			return addsql + " " + querySQL.toString();
		}
		return "";
	}

	/**
	 * 执行数据表 增删改 单句操作
	 * 
	 * @param str
	 *            执行操作语句
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 * 
	 */
	public static final String execSqlDb(String str,
			javax.servlet.http.HttpServletRequest request) {
		return execSql(str);
	}

	/**
	 * 执行数据表 增删改 批量操作
	 * 
	 * @param str
	 *            执行操作语句
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 * 
	 */
	public static final String execSqlDb(ArrayList tmparr,
			javax.servlet.http.HttpServletRequest request) {
		return execSql(tmparr);
	}

	/**
	 * 执行无返回值存储过程
	 * 
	 * @param str
	 *            存储过程名称
	 * @param ArrayList
	 *            传入参数集合(全部为字符串)
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 * 
	 */
	public static final String execProc(String str, ArrayList tmparr) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname =tmpvo.readProperty( "dbType");


		Connection con = getConnect(dbname);
		CallableStatement cstmt = null;

		if (con == null)
			return "error";
		try {
			CallableStatement proc = null;
			str = " { call " + str.trim() + "(";
			for (int s = 0; s < tmparr.size(); s++) {
				str += "?,";
			}
			if (str.endsWith(",")) {
				str = str.substring(0, str.length() - 1);
			}
			str += ")}";

			proc = con.prepareCall(str);
			for (int s = 0; s < tmparr.size(); s++) {
				proc.setString(s + 1, (String) tmparr.get(s));
			}
			proc.execute();

			con.close();
			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				cstmt.close();
				con.close();
				con = null;
				return String.valueOf(se.toString() + " 调用参数-"
						+ tmparr.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			cstmt = null;
		}
	}

	/**
	 * 执行有返回值存储过程
	 * 
	 * @param str
	 *            存储过程名称
	 * @param ArrayList
	 *            传入参数集合(全部为字符串)
	 * @param int 几个返回值 (全部为字符串)
	 * @return-- Arraylist 返回值集合
	 * 
	 */
	public static final ArrayList execProc(String str, ArrayList tmparr,
			int nretnum) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname =tmpvo.readProperty( "dbType");
		String debugsign = tmpvo.readProperty("debugsign");
		if (debugsign.equalsIgnoreCase("yes")) {
			System.out.println("调用存储过程 " + str + "  进入参数:" + tmparr.toString());
		}
		Connection con = getConnect(dbname);
		CallableStatement cstmt = null;

		if (con == null)
			return new ArrayList();
		try {
			ArrayList tmparrret = new ArrayList();
			CallableStatement proc = null;
			str = " { call " + str.trim() + "(";
			for (int s = 0; s < tmparr.size(); s++) {
				str += "?,";
			}
			for (int s = 0; s < nretnum; s++) {
				str += "?,";
			}

			if (str.endsWith(",")) {
				str = str.substring(0, str.length() - 1);
			}
			str += ")}";

			proc = con.prepareCall(str);
			int ntotonum = tmparr.size() + 1;
			for (int s = 0; s < tmparr.size(); s++) {
				proc.setString(s + 1, (String) tmparr.get(s));
			}
			for (int s = 0; s < nretnum; s++) {
				proc.registerOutParameter(s + ntotonum, Types.VARCHAR);
			}
			proc.execute();
			for (int s = 0; s < nretnum; s++) {
				String retvalue = proc.getString(s + ntotonum);
				tmparrret.add(retvalue);
			}

			con.close();
			con = null;
			if (debugsign.equalsIgnoreCase("yes")) {
				System.out.println("执行存储过程：" + str + "  返回:"
						+ tmparrret.toString());
			}
			return tmparrret;

		} catch (Exception se) {
			se.printStackTrace();
			try {
				cstmt.close();
				// freeConnect(con);
				con.close();
				con = null;
				return new ArrayList();
			} catch (SQLException se1) {
				se1.printStackTrace();
				return new ArrayList();
			}
		} finally {
			con = null;
			cstmt = null;
		}
	}

	/**
	 * 执行有返回值存储过程
	 * 
	 * @param str
	 *            存储过程名称
	 * @param ArrayList
	 *            参数集合(全部为字符串)
	 * @param int 结果集有几个返回值 (全部为字符串)
	 * @return-- Arraylist 返回值集合(String[])
	 * 
	 */
	public static final ArrayList execProcSet(String str, ArrayList tmparr,
			int nretnum) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname =tmpvo.readProperty( "dbType");

		Connection con = getConnect(dbname);
		CallableStatement cstmt = null;
		ResultSet rs = null;

		if (con == null)
			return new ArrayList();
		try {
			ArrayList tmparrret = new ArrayList();
			CallableStatement proc = null;
			str = " { call " + str.trim() + "(";
			for (int s = 0; s < tmparr.size(); s++) {
				str += "?,";
			}
			str += "?";
			str += ")}";

			proc = con.prepareCall(str);
			int ntotonum = tmparr.size() + 1;
			for (int s = 0; s < tmparr.size(); s++) {
				proc.setString(s + 1, (String) tmparr.get(s));
			}
			proc.registerOutParameter(ntotonum, oracle.jdbc.OracleTypes.CURSOR);
			proc.execute();
			rs = (ResultSet) proc.getObject(ntotonum);

			while (rs.next()) {
				String[] tmpstr = new String[nretnum];
				for (int s = 0; s < nretnum; s++) {
					tmpstr[s] = rs.getString(s + 1);
				}
				tmparrret.add(tmpstr);
			}
			if (rs != null) {
				rs.close();
			}
			con.close();
			con = null;
			return tmparrret;

		} catch (Exception se) {
			se.printStackTrace();
			try {
				rs.close();
				cstmt.close();
				con.close();
				con = null;
				return new ArrayList();
			} catch (SQLException se1) {
				se1.printStackTrace();
				return new ArrayList();
			}
		} finally {
			con = null;
			cstmt = null;
		}
	}

	/**
	 * 将文件以二进制方式写入BLOB字段
	 * 
	 * @param filename
	 *            文件名称- 含路径
	 * @param wheresql
	 *            where 条件
	 * @param tablename
	 *            数据表名称
	 * @param fieldname
	 *            字段名称
	 * @return ok /出错信息
	 */

	public static final String updateBlob(String filename, String wheresql,
			String tablename, String fieldname) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname =tmpvo.readProperty( "dbType");


		String updsql = "update " + tablename + " set " + fieldname
				+ "=? where " + wheresql;
		File files = new File(filename);
		long l1 = files.length();
		String ret = "error";
		int l2 = (int) l1;
		try {
			FileInputStream fis = new FileInputStream(files);
			Connection con = getConnect(dbname);
			PreparedStatement ps = con.prepareStatement(updsql);

			ps.setBinaryStream(1, fis, l2);
			ps.executeUpdate();
			ps.close();
			fis.close();
			con.close();
			return "ok";
		} catch (Exception e) {
			e.printStackTrace();
		}

		return ret;
	}

	public List<Object> makePageList(HashMap<String, String> hashmap,
			int npage, int nrows, int ntotrec) {
		String withsql = (String) hashmap.get("fd_WithSql".toLowerCase());
		String selesql = (String) hashmap.get("fd_SearchSql".toLowerCase());
		String fromsql = (String) hashmap.get("fd_FromSql".toLowerCase());
		String wheresql = (String) hashmap.get("fd_WhereSql".toLowerCase());
		String ordersql = (String) hashmap.get("fd_OrderSql".toLowerCase());
		String groupsql = (String) hashmap.get("fd_GroupSql".toLowerCase());
		
		List<Object> tmplist = new ArrayList<Object>();

		int beginrec = (npage - 1) * nrows;
		if (beginrec > ntotrec) {
			beginrec = 0;
		}
		String sqlstr = DBOperator.initQuerySQL(selesql, fromsql, wheresql,
				ordersql, beginrec, nrows, groupsql, withsql);
		DataSimpDto tmpdto = new DataSimpDto(sqlstr);

		for (int s = 0; s < tmpdto.getTotRecord(); s++) {
			Map<String, Object> tmpmap = new HashMap<String, Object>();
			for (int t = 0; t < tmpdto.getTotField(); t++) {
				String nowfield = tmpdto.getFieldName(t).toLowerCase();
				tmpmap.put(nowfield.toLowerCase(),
						tmpdto.getDataValue(nowfield, s));
			}
			tmplist.add(tmpmap);
		}
		return tmplist;
	}

	public List<Object> makeSumPageList(HashMap<String, String> hashmap,
			String fd_tjzdmc) {
		String fromsql = (String) hashmap.get("fd_FromSql".toLowerCase());
		String wheresql = (String) hashmap.get("fd_WhereSql".toLowerCase());
		String sumsql = (String) hashmap.get("fd_SumSql".toLowerCase());

		List<Object> tmplist = new ArrayList<Object>();

		String sqlstr = sumsql + "  " + fromsql + " " + wheresql;
		DataSimpDto tmpdto = new DataSimpDto(sqlstr);

		for (int s = 0; s < tmpdto.getTotRecord(); s++) {
			Map<String, Object> tmpmap = new HashMap<String, Object>();
			for (int t = 0; t < tmpdto.getTotField(); t++) {
				String nowfield = tmpdto.getFieldName(t).toLowerCase();
				tmpmap.put(nowfield.toLowerCase(),
						tmpdto.getDataValue(nowfield, s));
			}
			tmpmap.put(fd_tjzdmc, "合计");
			tmplist.add(tmpmap);
		}
		return tmplist;
	}

}
